package member;

import java.sql.*;
import member.MemberInfo;

public class DataManager {
	Connection conn 	= null;
	//url = "jdbc:oracle:thin:@" + serverName + ":1521:logis";
	String url		= "jdbc:oracle:thin:@localhost:1521:xe";
	String user		= "jsp_user";
	String pass		= "jsp_user";
	
	private Connection openConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, user, pass);
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
	public int insertMember(MemberInfo member) {
		PreparedStatement pstmt = null;
		String query = "insert into member values(?,?,?,?,?,?)";
		int res = 0;
		
		conn = openConnection();
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, member.getId());
			pstmt.setString(2, member.getPass());
			pstmt.setString(3, member.getName());
			pstmt.setString(4, member.getPhone());
			pstmt.setString(5, member.getEmail());
			Timestamp ts = new Timestamp(System.currentTimeMillis());
			pstmt.setTimestamp(6, ts);
			res = pstmt.executeUpdate();		
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			closeConnection();
		}
		
		return res;
	}
	
	public int removeMember(String id) {
		PreparedStatement pstmt = null;
		String query = "delete from member where id=?";
		int res = 0;
		
		conn = openConnection();
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, id);
			res = pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeConnection();
		}
		
		return res;
	}
	
	public int updateMember(MemberInfo member) {
		PreparedStatement pstmt = null;
		int res = 0;
		String query = "update member set pass=?, name=?, phone=?, email=? where id =?";
		
		conn = openConnection();
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, member.getPass());
			pstmt.setString(2, member.getName());
			pstmt.setString(3, member.getPhone());
			pstmt.setString(4, member.getEmail());
			pstmt.setString(5, member.getId());
			res = pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeConnection();
		}
		
		return res;
	}
	
	public boolean isMember(String id, String pass) {
		PreparedStatement pstmt = null;
		boolean res = false;
		String query = "select * from member where id =? and pass=?";
		
		conn = openConnection();
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, id);
			pstmt.setString(2, pass);
			ResultSet rs = pstmt.executeQuery();
			res = rs.next();
			rs.close();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeConnection();
		}
		
		return res;
	}
	
	public MemberInfo getMember(String id) {
		PreparedStatement pstmt = null;
		MemberInfo member = new MemberInfo();
		String query = "select * from member where id=?";
		conn = openConnection();
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			member.setId(rs.getString("id"));
			member.setPass(rs.getString("pass"));
			member.setName(rs.getString("name"));
			member.setPhone(rs.getString("phone"));
			member.setEmail(rs.getString("email"));
			member.setReg_date(rs.getTimestamp("reg_date"));
			rs.close();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			closeConnection();
		}
		
		return member;
	}
	
	private void closeConnection() {
		try {
			if(conn != null) {
				conn.close();
			}				
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			conn = null;
		}
	}
}